DROP TABLE IF EXISTS jms_dws.dws_order_hour_cnt_dt;
CREATE EXTERNAL TABLE jms_dws.dws_order_hour_cnt_dt(
         input_date                    string     comment  '录入日期'
         ,input_hour                   string     comment  '录入小时'
         ,order_source_code            string     comment  '订单来源code'
         ,order_source_name            string     comment  '订单来源名称'
         ,pick_network_code            string     comment  '寄件网点code'
         ,pick_network_name            string     comment  '寄件网点名称'
         ,pick_franchisee_code         string     comment  '寄件加盟商code'
         ,pick_franchisee_name         string     comment  '寄件加盟商名称'
         ,pick_agent_code              string     comment  '寄件代理区code'
         ,pick_agent_name              string     comment  '寄件代理区名称'
         ,sender_province_id           bigint     comment  '寄件省份id'
         ,sender_province_name         string     comment  '寄件省份'
         ,sender_city_id               bigint     comment  '寄件城市id'
         ,sender_city_name             string     comment  '寄件城市名称'
         ,sender_area_id               bigint     comment  '寄件区县id'
         ,sender_area_name             string     comment  '寄件区县名称'
         ,delete_cnt                   bigint     comment  '删除单量'
         ,cancel_cnt                   bigint     comment  '取消单量'
         ,normal_cnt                   bigint     comment  '正常单量'
         ,all_cnt                      bigint     comment  '所有单量'
 )
 COMMENT '订单综合统计汇总表(小时)'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_dws.db/external/dws_order_hour_cnt_dt'
 TBLPROPERTIES (
 'parquet.column.index.access'='true'
 );


DROP TABLE IF EXISTS jms_dws.dws_order_day_cnt_dt;
CREATE EXTERNAL TABLE jms_dws.dws_order_day_cnt_dt(
         input_date                    string     comment  '录入日期'
         ,order_source_code            string     comment  '订单来源code'
         ,order_source_name            string     comment  '订单来源名称'
         ,pick_network_code            string     comment  '寄件网点code'
         ,pick_network_name            string     comment  '寄件网点名称'
         ,pick_franchisee_code         string     comment  '寄件加盟商code'
         ,pick_franchisee_name         string     comment  '寄件加盟商名称'
         ,pick_agent_code              string     comment  '寄件代理区code'
         ,pick_agent_name              string     comment  '寄件代理区名称'
         ,sender_province_id           bigint     comment  '寄件省份id'
         ,sender_province_name         string     comment  '寄件省份'
         ,sender_city_id               bigint     comment  '寄件城市id'
         ,sender_city_name             string     comment  '寄件城市名称'
         ,sender_area_id               bigint     comment  '寄件区县id'
         ,sender_area_name             string     comment  '寄件区县名称'
         ,delete_cnt                   bigint     comment  '删除单量'
         ,cancel_cnt                   bigint     comment  '取消单量'
         ,normal_cnt                   bigint     comment  '正常单量'
         ,all_cnt                      bigint     comment  '所有单量'
 )
 COMMENT '订单综合统计汇总表(天)'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_dws.db/external/dws_order_day_cnt_dt'
 TBLPROPERTIES (
 'parquet.column.index.access'='true'
 );